#!/bin/usr/python
#encoding=utf8

#----------------------------------
#   author   : albertcheng
#   date     : 2012-10-28
#   file     : dbo.py
#----------------------------------
import sqlite3
import sys,os
import re
import time
try:
    import MySQLdb
except:
    print "import MySQLdb failed. check your python enviroment."
    sys.exit(1)
from lxml import etree

class DBO:
    
    def __init__(self,rd):
        host = None
        for line in rd:
            if str(line[1]) == "1":
                host = line[2]
                port = line[3]
                user = line[4]
                passwd = line[5]
                charset = line[6]
                db = "mysql"
        if host == None:
            host = rd[0][2]
            port = rd[0][3]
            user = rd[0][4]
            passwd = rd[0][5]
            charset = rd[0][6]
            db = "mysql"
        self.dbc = {
            "host"    : host,
            "user"    : user,
            "passwd"  : passwd,
            "db"      : db,
            "port"    : port,
            "charset" : charset
        }
        
        self.name_sql = "set names '%s';" % self.dbc["charset"]
        
    def test_conn(self):
        try:
            c = MySQLdb.connect(self.dbc["host"],self.dbc["user"],self.dbc["passwd"],self.dbc["db"],self.dbc["port"],self.dbc["charset"])
            c.close()
            return 0
        except MySQLdb.Error,e:
            return e
        
        
    def __conn(self):
        try:
            self.conn= MySQLdb.connect(self.dbc["host"],self.dbc["user"],self.dbc["passwd"],self.dbc["db"],self.dbc["port"],self.dbc["charset"])#,use_unicode=True)
            self.cur= self.conn.cursor() 
            
        except MySQLdb.Error,e: 
            error =  "Mysql Error %d: %s" % (e.args[0], e.args[1])
            #print error
            return error 
    
    def __close(self):
        try:
            self.cur.close()
            self.conn.close()
            
        except MySQLdb.Error,e: 
            error =  "Mysql Error %d: %s" % (e.args[0], e.args[1])
            #print error
            return error   
             
    def __extension(self,s):
        #print s
        s = s.strip()
        if s != "":
            if s[-1] != ";":
                s = s+";"
            return s
    
        
    def dosql(self,sql):
        
        if type(sql) != type(list()):
            l = [sql,]
        else:
            l = sql
        return self.dosqlmany(l)
        
    def dosqlmany(self,sql):
        
        self.__conn()
        
        if type(sql) != type(list()):
            print "method-dosqlmany() needs a list argus."
            return None
        else:
            sql.insert(0,self.name_sql)#插入命名
            
            for line in sql:
                #self.__extension(line)
                try:
                    self.cur.execute(line)
                except:
                    line = line.encode("utf-8")
                    try:
                        self.cur.execute(line)
                    except:
                        pass
            self.conn.commit()
            rd = self.cur.fetchall()
        self.__close()
        return rd
        
    def dosqlwithvalue(self,sql,value):
        self.__conn()
        self.__extension(sql)
        self.cur.execute(sql,value)
        rd = self.cur.fetchall()
        self.conn.commit()
        self.__close()
        return rd
        
    def dosqlmanywithvalues(self,sql):
        self.__conn()
        
        if type(sql) != type(list):
            print "method-dosqlmany() needs a list argus."
        else:
            for line in sql:
                if type(line) == type(str):
                    self.__extension(sql)
                    self.cur.execute(line)
                elif type(line) == type(list) or type(line) == type(type):
                    nsql = line[0]
                    nvalues = line[1]
                    ntype = line[2]
                    if ntype == 2:
                        self.__extension(nsql)
                        self.cur.executemany(nsql,nvalues)
                    elif ntype == 1:
                        self.__extension(nsql)
                        self.cur.execute(nsql,nvalues)
                    else:
                        print "wrong sql ntype."
            rd = self.cur.fetchall()
            self.conn.commit()
        self.__close()
        return rd
    
    def getroledata(self,db,tb,index):
        sql = "select * from %s.%s where uc_id='%s' or name='%s';" % (db,tb,index,index)
        return self.dosql(sql)
        
 
class sdbo:
    def __init__(self):
        self.__tbinit()
        
    def __conn(self):
        self.conn = sqlite3.connect(self.dbfile)
        self.cur  = self.conn.cursor()
    
    def __close(self):
        self.conn.close()
        
    def dosql(self,sql):
        self.__conn()
        for line in sql:
            self.cur.execute(line)
        rd = self.cur.fetchall()
        self.conn.commit()
        self.__close()
        return rd
    
    def __tbinit(self):
        self.dbfile = "db/config.db"
        sql_1 = ["select name from sqlite_master where type='table' order by name;",]
        self.tbl = self.dosql(sql_1)
        
        
        sqls = []
        tbn = [(u'config',),(u'prefer',)]
        
        for ntb in tbn:
            if ntb not in self.tbl:
                sqls.append("DROP TABLE IF EXISTS %s" % ntb)
                if ntb[0] == "prefer":
                    sqls.append("""create table prefer( id INTEGER PRIMARY KEY AUTOINCREMENT,
                                                        lastProjId integer);
                                                      """)
                    sqls.append("insert into prefer values(null,'0');")
                elif ntb[0] == "config":
                    sqls.append("""
            create table config( id INTEGER PRIMARY KEY AUTOINCREMENT,
                                     type integer,
                                     host varchar(15),
                                     port interger,
                                     user varchar(20), 
                                     password varchar(20),
                                     charset varchar(15));
                             """)
                    sqls.append("insert into config values(null,'1','192.168.105.23','3306','root','1234','utf8');")
        self.dosql(sqls)
            
        time.sleep(1)
    def add(self,host,port,user,passwd,charset):
        self.__updateall()
        
        sql = ["insert into config values(null,1, '%s', %d, '%s', '%s', '%s');" % (host,int(port),user,passwd,charset),
               ]
        self.dosql(sql)
        #sql = ["insert config values(null,1, '%s', %d, '%s', '%s', '%s')" % (),]
        
    def select(self,nid):
        sql = ["select * from config;",]
        tbd = self.dosql(sql)
        sqls = []
        ids = [] 

        if tbd != []:
            for n in tbd:
                id_id = n[0]
                if int(id_id) == int(nid):
                    s = "update config set type=1 where id=%s;" % id_id
                else:
                    s = "update config set type=0 where id=%s;" % n[0]
                ids.append(n[0])
                sqls.append(s)
            self.dosql(sqls)
        return ids
    
    def save(self,nid,host,port,user,passwd,charset):
        tbd = self.__updateall()
        ids = [int(x[0]) for x in tbd]
        if int(nid) not in ids:
            self.add(host,port,user,passwd,charset)
        else:
            sql = "update config set type=1,host='%s',port=%s,user='%s',password='%s',charset='%s'  where id=%s;" % (host,int(port),user,passwd,charset,nid)
            #print sql
            self.dosql([sql,])
            
            
    def delete(self,nid):
        
        tbd = self.__updateall()
        if tbd != []:
            #print tbd
            ids = [int(x[0]) for x in tbd]
            
            if int(nid) not in ids:
                return
            else:
                sql = "delete from config where id=%s;" % nid
                #print sql
                self.dosql([sql,])
        
    def __updateall(self):
        sql = ["select * from config;",]
        tbd = self.dosql(sql)
        sqls = []
        if tbd != []:
            for n in tbd:
                s = "update config set type=0 where id=%s;" % n[0]
                sqls.append(s)
            self.dosql(sqls)
        return tbd
    
    def delall(self):
        sql = ["DROP TABLE IF EXISTS config;",]
        self.dosql(sql)
        self.__tbinit()
        
        
    def get(self):
        sqls = []
        for ntb in self.tbl:
            if ntb == (u'sqlite_sequence',):
                continue
            sqls.append(["select * from %s;" % ntb,])
        
        d = []
        for ns in sqls:
            rd = self.dosql(ns)
            d.append(rd)
        return d


class get_sql:
    def __init__(self):
        pass
        
    def get(self):
        f = open("sql.txt","r")
        self.fd = f.readlines()
        f.close()
        
        sqld = {}
        if self.fd != []:
            
            for line in self.fd:
                if "|" in line:
                    sl = line.split('|')
                    proj = sl[0].strip()
                    tbname = sl[1].strip()
                    action = sl[2].strip()
                    sql = sl[3].strip()
                    if not sqld.has_key(proj):                    
                        sqld[proj] = {}
                    if not sqld[proj].has_key(tbname):
                        sqld[proj][tbname] = ["","","",""]#True]
                    if action == "query":         
                        sqld[proj][tbname][0] = sql
                    elif action == "update":
                        sqld[proj][tbname][1] = sql
                    elif action == "insert":
                        sqld[proj][tbname][2] = sql
                    elif action == "delete":
                        sqld[proj][tbname][3] = sql
        return sqld
    
    def getxml(self,proj,fn,tag):
        fn = os.getcwdu()+"//xml//%s//" % proj+fn
        
        if os.path.exists(fn):
            f = open(fn,"r")
            fd = f.readlines()
            f.close()
            rd = []
            if fd != []:
                for line in fd:
                    if "<!-- <" in line:
                        continue
                    if tag in line:
                        a = re.findall(r'="[^"]*"',line)
                        a = [x.split('"')[1] for x in a if '="' in x]
                        rd.append(a)
                return rd
        else:
            print "failed"
            return []

  
#a = sdbo()
#print a.showalldata()
#nid,host,port,user,passwd,charset
#a.save("1","localhost","3306","root","1234","utf8")